#!/bin/bash

# binlog_parser.sh - 解析MySQL binlog并生成SQL语句
# 用法: ./binlog_parser.sh <binlog_file> <start_pos> <stop_pos> <type> [reverse]

set -e

# 颜色定义
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m' # No Color

# 显示帮助信息
show_help() {
    echo -e "${BLUE}MySQL Binlog 解析器${NC}"
    echo ""
    echo "用法: $0 <binlog_file> <start_pos> <stop_pos> <type> [reverse]"
    echo ""
    echo "参数:"
    echo "  binlog_file   - binlog文件路径"
    echo "  start_pos     - 开始位置"
    echo "  stop_pos      - 结束位置"
    echo "  type          - 操作类型 (delete, insert, update, all)"
    echo "  reverse       - 可选参数，生成反向SQL语句"
    echo ""
    echo "示例:"
    echo "  $0 /var/lib/mysql/mysql-bin.000001 1000 2000 delete"
    echo "  $0 /var/lib/mysql/mysql-bin.000001 1000 2000 delete reverse"
    echo "  $0 /var/lib/mysql/mysql-bin.000001 1000 2000 all"
    echo ""
    echo "支持的操作类型:"
    echo "  delete  - 解析DELETE操作"
    echo "  insert  - 解析INSERT操作"
    echo "  update  - 解析UPDATE操作"
    echo "  all     - 解析所有操作"
    echo ""
    echo "反向操作说明:"
    echo "  DELETE -> INSERT"
    echo "  INSERT -> DELETE"
    echo "  UPDATE -> UPDATE (交换前后值)"
}

# 检查参数
check_params() {
    if [ $# -lt 4 ]; then
        echo -e "${RED}错误: 参数不足${NC}"
        show_help
        exit 1
    fi

    BINLOG_FILE="$1"
    START_POS="$2"
    STOP_POS="$3"
    OPERATION_TYPE="$4"
    REVERSE="$5"

    # 检查binlog文件是否存在
    if [ ! -f "$BINLOG_FILE" ]; then
        echo -e "${RED}错误: binlog文件不存在: $BINLOG_FILE${NC}"
        exit 1
    fi

    # 检查位置参数是否为数字
    if ! [[ "$START_POS" =~ ^[0-9]+$ ]] || ! [[ "$STOP_POS" =~ ^[0-9]+$ ]]; then
        echo -e "${RED}错误: 位置参数必须是数字${NC}"
        exit 1
    fi

    # 检查操作类型
    case "$OPERATION_TYPE" in
        delete|insert|update|all)
            ;;
        *)
            echo -e "${RED}错误: 不支持的操作类型: $OPERATION_TYPE${NC}"
            echo "支持的类型: delete, insert, update, all"
            exit 1
            ;;
    esac
}

# 解析DELETE操作
parse_delete() {
    local reverse="$1"
    local output_file="$2"
    
    echo -e "${BLUE}解析DELETE操作...${NC}"
    
    if [ "$reverse" = "true" ]; then
        echo "-- DELETE反向操作: INSERT语句" >> "$output_file"
        echo "-- 生成时间: $(date)" >> "$output_file"
        echo "" >> "$output_file"
        
        # 解析DELETE并生成INSERT
        awk '
        /^### DELETE FROM/ {
            table_name = $4
            gsub(/`/,"",table_name)
            printf "INSERT INTO %s (", table_name
        }
        /^### WHERE/ {
            in_where = 1
            col_count = 0
            next
        }
        /^###   @[0-9]+=/ && in_where {
            col_count++
            if (col_count > 1) printf ", "
            printf "col" col_count
        }
        /^###   @[0-9]+=/ && in_where {
            value = substr($0, index($0, "=") + 1)
            gsub(/^[ \t]+/, "", value)
            gsub(/^'\''|'\''$/, "", value)
            values[col_count] = value
        }
        /^###   @[0-9]+=FROM_UNIXTIME/ && in_where {
            value = substr($0, index($0, "FROM_UNIXTIME(") + 13)
            value = substr(value, 1, index(value, ")") - 1)
            values[col_count] = "FROM_UNIXTIME(" value ")"
        }
        /^###   @[0-9]+=[0-9]+$/ && in_where {
            value = substr($0, index($0, "=") + 1)
            gsub(/^[ \t]+/, "", value)
            values[col_count] = value
        }
        /^###   @[0-9]+=NULL$/ && in_where {
            values[col_count] = "NULL"
        }
        /^[^#]/ && in_where {
            in_where = 0
            printf ") VALUES ("
            for (i = 1; i <= col_count; i++) {
                if (i > 1) printf ", "
                printf values[i]
            }
            print ");"
            print ""
        }
        ' temp_binlog.txt >> "$output_file"
    else
        echo "-- DELETE操作" >> "$output_file"
        echo "-- 生成时间: $(date)" >> "$output_file"
        echo "" >> "$output_file"
        
        # 提取原始DELETE语句
        awk '
        /^### DELETE FROM/ {
            print $0
        }
        /^### WHERE/ {
            print $0
        }
        /^###   @[0-9]+=/ {
            print $0
        }
        /^[^#]/ && in_where {
            in_where = 0
            print ""
        }
        ' temp_binlog.txt >> "$output_file"
    fi
}

# 解析INSERT操作
parse_insert() {
    local reverse="$1"
    local output_file="$2"
    
    echo -e "${BLUE}解析INSERT操作...${NC}"
    
    if [ "$reverse" = "true" ]; then
        echo "-- INSERT反向操作: DELETE语句" >> "$output_file"
        echo "-- 生成时间: $(date)" >> "$output_file"
        echo "" >> "$output_file"
        
        # 解析INSERT并生成DELETE
        awk '
        /^### INSERT INTO/ {
            table_name = $4
            gsub(/`/,"",table_name)
            printf "DELETE FROM %s WHERE ", table_name
        }
        /^### SET/ {
            in_set = 1
            col_count = 0
            next
        }
        /^###   @[0-9]+=/ && in_set {
            col_count++
            if (col_count > 1) printf " AND "
            printf "col" col_count " = "
            value = substr($0, index($0, "=") + 1)
            gsub(/^[ \t]+/, "", value)
            printf value
        }
        /^[^#]/ && in_set {
            in_set = 0
            print ";"
            print ""
        }
        ' temp_binlog.txt >> "$output_file"
    else
        echo "-- INSERT操作" >> "$output_file"
        echo "-- 生成时间: $(date)" >> "$output_file"
        echo "" >> "$output_file"
        
        # 提取原始INSERT语句
        awk '
        /^### INSERT INTO/ {
            print $0
        }
        /^### SET/ {
            print $0
        }
        /^###   @[0-9]+=/ {
            print $0
        }
        /^[^#]/ && in_set {
            in_set = 0
            print ""
        }
        ' temp_binlog.txt >> "$output_file"
    fi
}

# 解析UPDATE操作
parse_update() {
    local reverse="$1"
    local output_file="$2"
    
    echo -e "${BLUE}解析UPDATE操作...${NC}"
    
    if [ "$reverse" = "true" ]; then
        echo "-- UPDATE反向操作: 交换前后值" >> "$output_file"
        echo "-- 生成时间: $(date)" >> "$output_file"
        echo "" >> "$output_file"
        
        # 解析UPDATE并生成反向UPDATE
        awk '
        /^### UPDATE/ {
            table_name = $3
            gsub(/`/,"",table_name)
            printf "UPDATE %s SET ", table_name
        }
        /^### WHERE/ {
            in_where = 1
            next
        }
        /^### SET/ {
            in_set = 1
            next
        }
        /^###   @[0-9]+=/ && in_set {
            col_num = substr($0, 5, index($0, "=") - 5)
            gsub(/^[ \t]+/, "", col_num)
            gsub(/=.*$/, "", col_num)
            value = substr($0, index($0, "=") + 1)
            gsub(/^[ \t]+/, "", value)
            set_values[col_num] = value
        }
        /^###   @[0-9]+=/ && in_where {
            col_num = substr($0, 5, index($0, "=") - 5)
            gsub(/^[ \t]+/, "", col_num)
            gsub(/=.*$/, "", col_num)
            value = substr($0, index($0, "=") + 1)
            gsub(/^[ \t]+/, "", value)
            where_values[col_num] = value
        }
        /^[^#]/ && (in_set || in_where) {
            if (in_set) {
                # 生成反向SET子句
                first = 1
                for (col in set_values) {
                    if (first) first = 0
                    else printf ", "
                    printf "col%s = %s", col, where_values[col]
                }
                printf " WHERE "
                # 生成WHERE子句
                first = 1
                for (col in where_values) {
                    if (first) first = 0
                    else printf " AND "
                    printf "col%s = %s", col, set_values[col]
                }
                print ";"
                print ""
            }
            in_set = 0
            in_where = 0
        }
        ' temp_binlog.txt >> "$output_file"
    else
        echo "-- UPDATE操作" >> "$output_file"
        echo "-- 生成时间: $(date)" >> "$output_file"
        echo "" >> "$output_file"
        
        # 提取原始UPDATE语句
        awk '
        /^### UPDATE/ {
            print $0
        }
        /^### WHERE/ {
            print $0
        }
        /^### SET/ {
            print $0
        }
        /^###   @[0-9]+=/ {
            print $0
        }
        /^[^#]/ && (in_set || in_where) {
            in_set = 0
            in_where = 0
            print ""
        }
        ' temp_binlog.txt >> "$output_file"
    fi
}

# 主函数
main() {
    # 检查参数
    check_params "$@"
    
    # 设置输出文件
    OUTPUT_FILE="binlog_${OPERATION_TYPE}_$(date +%Y%m%d_%H%M%S).sql"
    
    echo -e "${GREEN}开始解析binlog文件: $BINLOG_FILE${NC}"
    echo -e "${GREEN}位置范围: $START_POS - $STOP_POS${NC}"
    echo -e "${GREEN}操作类型: $OPERATION_TYPE${NC}"
    if [ "$REVERSE" = "reverse" ]; then
        echo -e "${GREEN}模式: 反向操作${NC}"
    else
        echo -e "${GREEN}模式: 正向操作${NC}"
    fi
    echo ""
    
    # 提取指定位置的binlog内容
    echo -e "${YELLOW}提取binlog内容...${NC}"
    mysqlbinlog --verbose --base64-output=DECODE-ROWS \
        --start-position="$START_POS" \
        --stop-position="$STOP_POS" \
        "$BINLOG_FILE" > temp_binlog.txt
    
    # 清空输出文件
    > "$OUTPUT_FILE"
    
    # 根据操作类型解析
    case "$OPERATION_TYPE" in
        delete)
            parse_delete "$([ "$REVERSE" = "reverse" ] && echo "true" || echo "false")" "$OUTPUT_FILE"
            ;;
        insert)
            parse_insert "$([ "$REVERSE" = "reverse" ] && echo "true" || echo "false")" "$OUTPUT_FILE"
            ;;
        update)
            parse_update "$([ "$REVERSE" = "reverse" ] && echo "true" || echo "false")" "$OUTPUT_FILE"
            ;;
        all)
            parse_delete "$([ "$REVERSE" = "reverse" ] && echo "true" || echo "false")" "$OUTPUT_FILE"
            parse_insert "$([ "$REVERSE" = "reverse" ] && echo "true" || echo "false")" "$OUTPUT_FILE"
            parse_update "$([ "$REVERSE" = "reverse" ] && echo "true" || echo "false")" "$OUTPUT_FILE"
            ;;
    esac
    
    # 清理临时文件
    rm -f temp_binlog.txt
    
    echo -e "${GREEN}解析完成！${NC}"
    echo -e "${GREEN}输出文件: $OUTPUT_FILE${NC}"
    echo ""
    echo -e "${YELLOW}生成的SQL语句:${NC}"
    echo "----------------------------------------"
    cat "$OUTPUT_FILE"
    echo "----------------------------------------"
    echo ""
    echo -e "${BLUE}执行SQL语句:${NC}"
    echo "mysql -u root < $OUTPUT_FILE"
}

# 如果直接运行脚本
if [[ "${BASH_SOURCE[0]}" == "${0}" ]]; then
    main "$@"
fi
